use master
go

create database Dwu
on
(
name='Dwu',
filename='DtestDwu.mdf',
size=5mb,
maxsize=100mb,
filegrowth=10mb
)
log on
(
name='Dwu_log',
filename='DtestDwu_log.ldf',
size=5mb,
maxsize=100mb,
filegrowth=10mb
)

go
use Dwu
go

create table stuinfo
(
stuNo char(5) primary key not null,
stuName nvarchar(20) not null,
stuAge char(3) not null,
stuAddress nvarchar(200), 
stuSeat int identity(1,1)not null,
stuSex char(1) check(stuSex in (1,0)) default(1) not null
)
go

insert into stuinfo(stuNo,stuName,stuAge,stuAddress,stuSex) values
('s2501','张秋利',20,'美国硅谷',1),
('s2502','李斯文',18,'湖北武汉',0),                 
('s2503','马文才',22,'湖南长沙',1),
('s2504','欧阳俊雄',21,'湖北武汉',0),
('s2505','梅超风',20,'湖北武汉',1),
('s2506','陈旋风',19,'美国硅谷',1),
('s2507','陈凤',20,'美国硅谷',0) 
go

create table stuexam
(
examNo int primary key identity(1,1),
stuNo char(5) references stuinfo(stuNo) not null,
writtenExam int not null,
labExam int not null
) 
go

insert stuexam(stuNo,writtenExam,labExam)values
('s2501',50,70),
('s2502',60,65),
('s2503',86,85),
('s2504',40,80),
('s2505',70,90),
('s2506',85,90)
go

select stuName,stuAge,writtenExam,labExam from stuexam
inner join stuinfo on stuexam.stuNo = stuinfo.stuNo
go


select stuName,stuAge,writtenExam,labExam from stuexam
inner join stuinfo on stuexam.stuNo = stuinfo.stuNo
where writtenExam  60 and labExam  60
go

select stuName,stuAge,writtenExam,labExam from stuinfo
left join stuexam on stuexam.stuNo = stuinfo.stuNo
go

select stuName,stuAge,writtenExam,labExam from stuexam
inner join stuinfo on stuexam.stuNo = stuinfo.stuNo
where stuAge = 20
order by writtenExam desc 
go

select stuSex,avg(labExam) from stuexam
inner join stuinfo on stuexam.stuNo = stuinfo.stuNo
group by stuSex
go

select stuSex,sum(writtenExam) from stuexam
inner join stuinfo on stuexam.stuNo = stuinfo.stuNo
group by stuSex
go